package com.delicious.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.delicious.dao.BaseDao;
import com.delicious.dao.FoodsDao;
import com.delicious.entity.Foods;
import com.sun.org.apache.bcel.internal.generic.NEW;

public class FoodsDaoImpl extends BaseDao implements FoodsDao {

	public List<Foods> getFoodsByPaging(int pageNum, int pageSize) {
		List<Foods> list = new ArrayList<Foods>();
		String sql = "select * from foods";
		openConnection();
		ResultSet rs = null;
		ArrayList keyword = new ArrayList();
		
		if(pageNum>0 && pageSize>0){
			sql += " limit ?,?";
			keyword.add(pageNum);
			keyword.add(pageSize);
		}
		
		try {
			rs = executeQuery(sql, keyword.toArray());
			while(rs.next()){
				int id = rs.getInt("id");
				String chineseName = rs.getString("chineseName");
				String englishName = rs.getString("englishName");
				int categoryId = rs.getInt("categoryId");
				String description = rs.getString("description");
				Foods f = new Foods(id, chineseName, englishName, categoryId, description);
				list.add(f);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection();
		}
		
		return list;
	}

	public Foods getFoodByID(int id) {
		Foods food = null;
		String sql = "select * from foods where id = ?";
		openConnection();
		ResultSet rs = null;
		
		try {
			rs = executeQuery(sql, new Object[]{id});
			while(rs.next()){
				String chineseName = rs.getString("chineseName");
				String englishName = rs.getString("englishName");
				int categoryId = rs.getInt("categoryId");
				String description = rs.getString("description");
				Foods f = new Foods(id, chineseName, englishName, categoryId, description);
				food = new Foods(id, chineseName, englishName, categoryId, description);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection();
		}
		
		return food;
	}

	public List<Foods> getAllFoods(int cid ,String name ) {
		List<Foods> list = new ArrayList<Foods>();
		String sql = "select * from foods f inner join category c on c.id = f.categoryid where 1=1";
		ArrayList word = new ArrayList();
		if(cid > 0 ){
			sql += " and categoryid = ?";
			word.add(cid);
		}
		if(name != null){
			sql += " and chineseName like ?";
			word.add("%"+name+"%");
		}
		openConnection();
		ResultSet rs = null;
		
		try {
			rs = executeQuery(sql, word.toArray());
			while(rs.next()){
				int id = rs.getInt("id");
				String chineseName = rs.getString("chineseName");
				String englishName = rs.getString("englishName");
				int categoryId = rs.getInt("categoryId");
				String description = rs.getString("description");
				Foods f = new Foods(id, chineseName, englishName, categoryId, description);
				list.add(f);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection();
		}
		
		return list ;
	}

	public List<Foods> getFoodsByNameAndCid(int pageNum, int pageSize,
			String name, int cid) {
		List<Foods> list = new ArrayList<Foods>();
		openConnection();
		ResultSet rs = null;
		String sql = "select * from foods where 1=1";
		ArrayList words = new ArrayList();
		
		if(cid > 0){
			sql += " and categoryid = ? ";
			words.add(cid);
		}
		
		if(name != null){
			sql += " and chineseName like ?";
			words.add("%"+name+"%");
		}
		
		if(pageNum > 0 && pageSize >0){
			sql += " limit ?,?";
			words.add((pageNum-1)*pageSize);
			words.add(pageSize);
		}
		try {
			rs = executeQuery(sql, words.toArray());
			while(rs.next()){
				int id = rs.getInt("id");
				String chineseName = rs.getString("chineseName");
				String englishName = rs.getString("englishName");
				int categoryId = rs.getInt("categoryId");
				String description = rs.getString("description");
				Foods f = new Foods(id, chineseName, englishName, categoryId, description);
				list.add(f);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection();
		}
		
		return list;
	}

	public boolean deleteDataById(int id) {
		String sql = "delete from foods where id = ?";
		openConnection();
		int rows = 0;
		try {
			rows = executeUpdate(sql, new Object[]{id});
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows > 0;
	}
	
	

	public boolean updateDate(Foods food) {
		ArrayList word = new ArrayList();
		String sql = "";
		if(new FoodsDaoImpl().checkFoods(food.getChineseName())){
			 sql = "UPDATE foods SET categoryid=? ,description=? WHERE chineseName = ?";
			 word.add(food.getCategoryId());
			 word.add(food.getDescription());
			 word.add(food.getChineseName());
		}else{
			sql = "insert into foods(chineseName,englishName,categoryid,description) values (?,?,?,?)";
			word.add(food.getChineseName());
			word.add(food.getEnglishName());
			word.add(food.getCategoryId());
			word.add(food.getDescription());
		}
		System.out.println(sql);
		for(Object w : word){
			System.out.println(w);
		}
		openConnection();
		int rows = 0 ;
		try {
			rows = executeUpdate(sql, word.toArray());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows>0;
	}
	
	public static void main(String[] args) {
		Foods food = new Foods(0, "汉堡", "hamburger", 2, "很神奇的食物来的哦");
		boolean result = new FoodsDaoImpl().updateDate(food);
		System.out.println(result);
	}

	public boolean checkFoods(String name) {
		String sql = "select * from foods where chineseName = ?";
		ResultSet rs = null;
		openConnection();
		
		try {
			rs = executeQuery(sql, new Object[]{name});
			if(rs.next())
				return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
}
